/*
 * CodeManager.java
 *
 * Created on August 10, 2007, 11:42 AM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */
package org.noteit.db;

import java.util.logging.Level;
import java.util.logging.Logger;
import org.noteit.gui.MainFrame;
import org.noteit.helper.note;
import org.localdb.*;

import java.io.File;
import java.sql.*;
import java.util.ArrayList;
import org.h2.tools.RunScript;
import org.noteit.helper.timetable;

/**
 *
 * @author Owner
 */
public class CodeManager {

    Connection con;
    String connDriver = "org.h2.Driver";
    String connPath = null;
    String connUsername = "root";
    String connPassword = "Ae!F1*v03GD Ae!F1*v03GD";
    locate loc = new locate("Config/conf.xml");

    /** Creates a new instance of CodeManager */
    public CodeManager() {
        this.setJDBCUrl(loc.start());
        try {
            Class.forName(connDriver).newInstance();
            con = DriverManager.getConnection(connPath, connUsername, connPassword);

        } catch (Exception e) {
            System.err.println("Exception: " + e.getMessage());
        }
        if (con == null) {
            System.out.println("Con not exist");
        } else {
            System.out.println("Con exist");
            initCheckDB();
        }
    }

    public Connection getConnection() {
        return con;
    }

    public void initCheckDB() {
        /**
         * Checks during connection init if the tables are available. 
         * If not available, it would call scripts to create necessary items
         */
        if (this.isDefaultTableExisting()) {
            System.out.println("Default table(s) exist...");
        } else {
            System.out.println("Default table(s) NOT exist ! \nSetting up default table(s) begin...");
            this.createDefaultTables("SQL/noteit-table-setup.sql");
            boolean ok = this.isDefaultTableExisting();
            if (ok == true) {
                System.out.println("Default table(s) have been SUCCESSFULLY setup !");
            } else {
                System.out.println("Default table(s) have FAILED setup !");
            }

        }
    }

    public void runscript(String sqlUrl) { // runs scripts

        try {
            RunScript run = new RunScript();
            run.execute(connPath, connUsername, connPassword, sqlUrl, null, true);

        } catch (SQLException ex) {
            ex.printStackTrace();
        }

    }

    public boolean isDefaultTableExisting() { // check if tables are existing

        boolean ok = false;
        try {
            PreparedStatement stmt = con.prepareStatement("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='PUBLIC'"); //need to revamp to ensure that all tables are intact

            ResultSet rs = stmt.executeQuery();
            if (rs != null) {
                if (rs.last() == true) {
                    ok = true;
                }
            }
        } catch (SQLException ex) {
            System.out.println("Tables not existing...");
        }
        return ok;
    }

    public void createDefaultTables(String filepath) { // Create all table from default script

        File file = new File(filepath);
        if (file.exists()) {
            runscript(file.getAbsolutePath());
        } else {
            System.out.println("Table setup script: " + file.toString() + " ,file is not found !\nFAILED to setup all tables.");
        }
    }

    public boolean addNote(String note) {
        boolean ok = false;

        try {
            PreparedStatement stmt = con.prepareStatement("INSERT into note(content) values(?)");
            stmt.setString(1, note);
            int num = stmt.executeUpdate();
            if (num == 1) {
                ok = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return ok;
    }

    public note getFirstNote() {
        System.out.println("Getting first note");
        note n = new note();
        boolean foundfirst = false;
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("Select * from note");
            System.out.println("Statement executed");
            while (foundfirst == false) {
                System.out.println("In while loop");
                rs.next();
                System.out.println("found data");
                n.setNoteid(rs.getInt("id"));
                n.setSubject(rs.getString("subject"));
                n.setContent(rs.getString("content"));
                if (n.getNoteid() >= 0) {
                    break;

                }
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        return n;
    }

    public note gotoId(String findId) throws NumberFormatException {
        int fId = 0;
        try {
            fId = Integer.parseInt(findId);
        } catch (NumberFormatException ex) {
            MainFrame m = MainFrame.getFrame();
            if (m != null) {
                m.NumberFormatExceptionAlert();
            }
            System.out.println("NFE found");
        }
        note n = new note();
        int tempid = 0;
        String tempsub = "";
        String tempcontent = "";
        boolean found = false;
        try {
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("Select * from note");
            while (found == false) {

                rs.next();
                System.out.println("found data");
                tempid = (rs.getInt("id"));
                tempsub = (rs.getString("subject"));
                tempcontent = (rs.getString("content"));
                if (tempid == fId) {
                    n.setNoteid(tempid);
                    n.setSubject(tempsub);
                    n.setContent(tempcontent);
                    found = true;
                    break;

                }
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        return n;
    }

    public note gotoSubject(String subject) {
        note n = null;
        try {
            PreparedStatement stmt = con.prepareStatement("Select * from note where subject= ?");
            stmt.setString(1, subject);
            ResultSet rs = stmt.executeQuery();
            if (rs != null) {
                rs.next();
                n = new note(rs.getInt("id"), rs.getString("subject"), rs.getString("content"));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return n;
    }

    public boolean deleteNote(String noteid) throws NumberFormatException {
        boolean ok = false;
        int id = 0;
        try {
            id = Integer.parseInt(noteid);
        } catch (NumberFormatException ex) {
            MainFrame m = MainFrame.getFrame();
            if (m != null) {
                m.NumberFormatExceptionAlert();
            }
            System.out.println("NFE found");
        }
        try {

            PreparedStatement stmt = con.prepareStatement("delete from note where id = ? ");

            stmt.setInt(1, id);

            int num = stmt.executeUpdate();
            if (num == 1) {
                ok = true;
            }
            stmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
            ok = false;
        }

        return ok;
    }

    public boolean checkId(String noteid) throws NumberFormatException {
        int id = 0;
        boolean ok = false;
        try {
            id = Integer.parseInt(noteid);
        } catch (NumberFormatException ex) {
            MainFrame m = MainFrame.getFrame();
            if (m != null) {
                m.NumberFormatExceptionAlert();
            }
            System.out.println("Executing NFE found");
            System.out.println("NFE found");
        }
        int currentId = 0;
        try {
            PreparedStatement stmt = con.prepareStatement("Select id from note");
            ResultSet rs = stmt.executeQuery();
            System.out.println("Statement executed");
            if (rs != null) {
                while (rs.next()) {
                    currentId = rs.getInt("id");
                    if (currentId == id) {
                        ok = true;
                    }
                }
            }
            stmt.close();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }

        return ok;
    }

    public boolean update(note n) throws NumberFormatException {
        boolean ok = false;

        try {

            PreparedStatement stmt = con.prepareStatement("UPDATE note SET content=? where subject=?");
            stmt.setString(1, n.getContent());
            stmt.setString(2, n.getSubject());
            int num = stmt.executeUpdate();
            if (num == 1) {
                ok = true;
            }
            stmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ok;
    }

    public note nextNote(String currSubject) {
        note note = new note();
        note temp = this.gotoSubject(currSubject);
        try {
            PreparedStatement stmt = con.prepareStatement("SELECT * FROM note WHERE id> ? ORDER BY id LIMIT 1");
            stmt.setInt(1, temp.getNoteid());
            ResultSet rs = stmt.executeQuery();
            if (rs != null) {
                rs.next();
                System.out.println("found data");
                note.setNoteid(rs.getInt("id"));
                note.setSubject(rs.getString("subject"));
                note.setContent(rs.getString("content"));
            }
        } catch (SQLException ex) {
            //ex.printStackTrace();
        }
        return note;
    }

    public note oldnext(String id) throws NumberFormatException {
        int nid = 0;
        try {
            nid = Integer.parseInt(id);
        } catch (NumberFormatException ex) {
            MainFrame m = MainFrame.getFrame();
            if (m != null) {
                m.NumberFormatExceptionAlert();
            }
            System.out.println(m);
            System.out.println("NFE found");
        }
        note n = new note();

        try {
            PreparedStatement stmt = con.prepareStatement("SELECT * FROM note WHERE id> ? ORDER BY id LIMIT 1");
            stmt.setInt(1, nid);
            ResultSet rs = stmt.executeQuery();
            if (rs != null) {
                rs.next();
                System.out.println("found data");
                n.setNoteid(rs.getInt("id"));
                n.setSubject(rs.getString("subject"));
                n.setContent(rs.getString("content"));
            }
            if (rs == null) {
                System.out.println("null resultset");
            }

        } catch (SQLException ex) {
            //ex.printStackTrace();
        }

        return n;
    }

    public note previousNote(String currSubject) {
        note note = new note();
        note temp = this.gotoSubject(currSubject);
        try {
            PreparedStatement stmt = con.prepareStatement("SELECT * FROM note WHERE id<? ORDER BY id DESC LIMIT 1");
            stmt.setInt(1, temp.getNoteid());
            ResultSet rs = stmt.executeQuery();
            if (rs != null) {
                rs.next();
                System.out.println("found data");
                note.setNoteid(rs.getInt("id"));
                note.setSubject(rs.getString("subject"));
                note.setContent(rs.getString("content"));
            }
            if (rs == null) {
                System.out.println("null resultset");
            }

        } catch (SQLException ex) {
            //ex.printStackTrace();
        }
        return note;
    }

    public note oldprevious(String id) throws NumberFormatException {
        int nid = 0;
        try {
            nid = Integer.parseInt(id);
        } catch (NumberFormatException ex) {
            MainFrame m = MainFrame.getFrame();
            if (m != null) {
                m.NumberFormatExceptionAlert();
            }
            System.out.println("NFE found");
        }
        note n = new note();

        try {
            PreparedStatement stmt = con.prepareStatement("SELECT * FROM note WHERE id<? ORDER BY id DESC LIMIT 1");
            stmt.setInt(1, nid);
            ResultSet rs = stmt.executeQuery();
            if (rs != null) {
                rs.next();
                System.out.println("found data");
                n.setNoteid(rs.getInt("id"));
                n.setSubject(rs.getString("subject"));
                n.setContent(rs.getString("content"));
            }
            if (rs == null) {
                System.out.println("null resultset");
            }

        } catch (SQLException ex) {
            //ex.printStackTrace();
        }

        return n;
    }

    public ArrayList getAllSubjectNames() {
        ArrayList aList = new ArrayList();
        try {
            PreparedStatement stmt = con.prepareStatement("SELECT subject FROM note");
            ResultSet rs = stmt.executeQuery();
            if (rs != null) {
                while (rs.next()) {
                    aList.add(rs.getString("subject"));
                }
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return aList;
    }

    public timetable getTimeTableByDay(String day) {
        timetable ttable = null;
        try {
            PreparedStatement stmt = con.prepareStatement("SELECT * FROM timetable where days=?");
            stmt.setString(1, day);
            ResultSet rs = stmt.executeQuery();
            if (rs != null) {
                rs.next();
                ttable = new timetable(rs.getString("content"), rs.getString("days"));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return ttable;
    }

    public ArrayList getAllTimeTable() {
        ArrayList aList = new ArrayList();
        timetable ttable = null;
        try {
            PreparedStatement stmt = con.prepareStatement("SELECT * FROM timetable");
            ResultSet rs = stmt.executeQuery();
            if (rs != null) {
                while (rs.next()) {
                    ttable = new timetable(rs.getString("content"), rs.getString("days"));
                    aList.add(ttable);
                }
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return aList;
    }

    public boolean setTimeTable(timetable timetable) {
        boolean ok = false;
        try {
            PreparedStatement stmt = con.prepareStatement("update timetable set content = ? where days = ?");
            stmt.setString(1, timetable.getContent());
            stmt.setString(2, timetable.getDays());
            int num = stmt.executeUpdate();
            if(num==1){
                ok = true;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return ok;
    }

    public void setJDBCUrl(String localdb) {
        connPath = "jdbc:h2:file:" + localdb + "/noteitdb;CIPHER=AES";
    }

    public void closeDB() {
        try {
            if (!con.isClosed()) {
                con.commit();
                con.close();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
}
